package com.lk.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import com.lk.utils.JdbcUtil;
import lombok.extern.slf4j.Slf4j;


import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;

@Slf4j
public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
    /**
     * 每隔500条存储数据库，实际使用中可以1000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 500;

    private List<Map<Integer, String>> cachedDataList = Lists.newArrayListWithExpectedSize(BATCH_COUNT);

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = Lists.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        Long begin = System.currentTimeMillis();

        // 获取jdbc连接
        Connection conn = JdbcUtil.getConnection();

        try {
            // 获取系统时间
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String time= LocalDateTime.now().format(formatter);

            StringBuilder suffix = new StringBuilder();
            // 设置事务为非自动提交
            conn.setAutoCommit(false);
            PreparedStatement pst = conn.prepareStatement("select 1");
            // 构建sql前缀
            String prefix = "insert into `t_gps_data`(`id`,`imei`,`gps_state`,`latitude`,`longitude`,`create_time`) values ";
            String id = null;
            // 外层循环，总提交事务次数
            for (Map<Integer, String> integerStringMap : cachedDataList) {
                // 构建sql后缀
                suffix.append("(").append(id).append(",");
                for (int i = 1; i < 5 ; i++) {
                    suffix.append(integerStringMap.get(i)).append(",");
                }
                suffix.append("\"").append(time).append("\"").append("),");
            }
            // 构建完整sql
            String sql = prefix+(suffix.substring(0, suffix.length()-1));
            log.info("执行的sql语句 :{}",sql);
            pst.addBatch(sql); // 添加执行sql
            pst.executeBatch();  // 执行操作
            conn.commit();     // 提交事务
            suffix.setLength(0);  // 清空上一次添加的数据
            // 头等连接
            pst.close();
        } catch (SQLException e) {
            log.error("存储数据库错误!" + e);
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 结束时间
        Long end =System.currentTimeMillis();
        log.info("耗时 time : " + (end - begin) / 1000 + " s");
    }
}

